<?php
namespace tool;
if (!defined('PHPEXCEL_ROOT')) {
	define('PHPEXCEL_ROOT', dirname(ROOT_PATH) . '/../vendor/PHPExcel/Classes/');

	require PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php';
}
class Excel {
	private $conf;
	private $header;
	private $cels = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	public function __construct($conf = []) {

	}
	public function setHeader($header = []) {
		$this->header = $header;
	}
	/**
	 * 创建(导出)Excel数据表格
	 * @param  array   $list        要导出的数组格式的数据
	 * @param  string  $filename    导出的Excel表格数据表的文件名
	 * @param  array   $indexKey    $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
	 * @param  array   $startRow    第一条数据在Excel表格中起始行
	 * @param  [bool]  $excel2007   是否生成Excel2007(.xlsx)以上兼容的数据表
	 * 比如: $indexKey与$list数组对应关系如下:
	 *     $indexKey = array('id','username','sex','age');
	 *     $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));
	 */
	function exportExcel($list, $filename, $indexKey = null, $startRow = 1, $excel2007 = false) {
		//文件引入
		if (empty($filename)) {
			$filename = time();
		}

		if (!is_array($indexKey) && empty($this->header)) {
			echo "未设置表头";
			return false;
		}

		$header_arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
		//初始化PHPExcel()
		$objPHPExcel = new \PHPExcel();

		//设置保存版本格式
		if ($excel2007) {
			$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
			$filename = $filename . '.xlsx';
		} else {
			$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
			$filename = $filename . '.xls';
		}

		//接下来就是写数据到表格里面去
		$objActSheet = $objPHPExcel->getActiveSheet();
		//$startRow = 1;
		if (!empty($this->header)) {
			$i = 0;
			foreach ($this->header as $key => $value) {
				//这里是设置单元格的内容
				$objActSheet->setCellValue($header_arr[$i] . "1", $value);
				$i++;
			}
			$startRow++;
			if (empty($indexKey)) {
				$indexKey = array_keys($this->header);
			}
		}
		foreach ($list as $row) {

			foreach ($indexKey as $key => $value) {
				if (isset($row[$value])) {
					//这里是设置单元格的内容
					$objActSheet->setCellValue($header_arr[$key] . $startRow, $row[$value]);
				}
			}
			$startRow++;
		}

		// 下载这个表格，在浏览器输出
		header("Pragma: public");
		header("Expires: 0");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Content-Type:application/force-download");
		header("Content-Type:application/vnd.ms-execl");
		header("Content-Type:application/octet-stream");
		header("Content-Type:application/download");
		header('Content-Disposition:attachment;filename=' . $filename . '');
		header("Content-Transfer-Encoding:binary");
		$objWriter->save('php://output');
	}
	public function readData(string $filepath, int $startLine = 1, int $sheetnum = 0) {
		$PHPReader = new \PHPExcel_Reader_Excel2007();

		//设置保存版本格式
		if (!$PHPReader->canRead($filepath)) {
			$PHPReader = new \PHPExcel_Reader_Excel5();
			if (!$PHPReader->canRead($filepath)) {
				echo '无法识别的Excel文件！';
				return false;
			}
		}
		//读取Excel
		$objExcel = $PHPReader->load($filepath);

		// 默认使用第一个工作簿，并获取行数
		$sheet = $objExcel->getActiveSheet($sheetnum);
		$rows = $sheet->getHighestRow();
		$datas = [];
		$cels = $this->cels;

		// 遍历，并读取单元格内容
		for ($i = $startLine; $i <= $rows; $i++) {

			foreach ($cels as $k => $v) {
				$cell = $sheet->getCell($v . $i)->getValue();
				// 开始格式化
				if (is_object($cell)) {
					$cell = $cell->__toString();
				}

				$datas[$i][$v] = $cell;
			}

		}
		return $datas;
	}
	/**
	 * 创建(导出)Excel数据表格
	 * @param  array   $list        要导出的数组格式的数据
	 * @param  string  $filename    导出的Excel表格数据表的文件名
	 * @param  array   $indexKey    $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
	 * @param  array   $startRow    第一条数据在Excel表格中起始行
	 * @param  [bool]  $excel2007   是否生成Excel2007(.xlsx)以上兼容的数据表
	 * 比如: $indexKey与$list数组对应关系如下:
	 *     $indexKey = array('id','username','sex','age');
	 *     $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));
	 */
	function readExcel($filepath, $sheetIndex = 0, $excel2007 = false) {
		//文件引入
		//  vendor("PHPExcel.Classes.PHPExcel");
		// require PHPEXCEL_ROOT.'PHPExcel/Reader/Excel2007.php';
		//初始化PHPExcel()
		$PHPReader = new \PHPExcel_Reader_Excel2007();

		//设置保存版本格式
		if (!$PHPReader->canRead($filepath)) {
			$PHPReader = new \PHPExcel_Reader_Excel5();
			if (!$PHPReader->canRead($filepath)) {
				echo '无法识别的Excel文件！';
				return false;
			}
		}
		//读取Excel
		$PHPExcel = $PHPReader->load($filepath);
		//读取工作表1
		$currentSheet = $PHPExcel->getSheet($sheetIndex);

		return $PHPExcel;
	}
	function export($filename, $objPHPExcel, $excel2007 = false) {
		//设置保存版本格式
		if ($excel2007) {
			$PHPWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
			$filename = $filename . '.xlsx';
		} else {
			$PHPWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
			$filename = $filename . '.xls';
		}
		ob_start();
		header("Content-Type: application/force-download");
		header("Content-Type: application/octet-stream");
		header("Content-Type: application/download");
		header('Content-Disposition:attachment;filename="' . $filename . '"'); //输出模板名称
		header("Content-Transfer-Encoding: binary");
		header("Last-Modified:" . gmdate("D, d M Y H:i:s") . " GMT");
		header('Pragma: public');
		header('Expires: 30');
		header('Cache-Control: public');
		$PHPWriter->save('php://output');
	}

}